﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BusinessLibrary.BLL;
using System.Data.SqlClient;
namespace BusinessLibrary.DAL
{
    class sqlEtudiant
    {
        private static Etudiant ReaderToObject(SqlDataReader dr)
        {
            Etudiant _etudiant = new Etudiant();
            _etudiant.Id = int.Parse(dr["Id"].ToString());

            _etudiant.Nom = dr["Nom"].ToString();
            _etudiant.Prenom = dr["Prenom"].ToString();
            _etudiant.Num_tel = dr["Num_tel"].ToString();
            _etudiant.Email = dr["Email"].ToString();
            _etudiant.Num_inscription = dr["Num_inscription"].ToString();




            if (!object.ReferenceEquals(dr["Id_niveau"], DBNull.Value))
            {
                _etudiant.niveau = Niveau.GetNiveauById(int.Parse(dr["Id_niveau"].ToString()));
            }

            if (!object.ReferenceEquals(dr["Id_classe"], DBNull.Value))
            {
                _etudiant.classe = Classe.GetClasseById(int.Parse(dr["Id_classe"].ToString()));
            }

            if (!object.ReferenceEquals(dr["Id_groupe"], DBNull.Value))
            {
                _etudiant.groupe = Groupe.GetGroupeById(int.Parse(dr["Id_groupe"].ToString()));
            }

            if (!object.ReferenceEquals(dr["Id_specialite"], DBNull.Value))
            {
                _etudiant.specialite = Specialite.GetSpecialiteById(int.Parse(dr["Id_specialite"].ToString()));
            }

            if (!object.ReferenceEquals(dr["Cin"], DBNull.Value))
            {
                _etudiant.Cin = int.Parse(dr["Cin"].ToString());
            }

            if (!object.ReferenceEquals(dr["Id_authentification"], DBNull.Value))
            {
                _etudiant.authentification = Authentification.GetAuthentificationById(int.Parse(dr["Id_authentification"].ToString()));
            }




            return _etudiant;
        }

        public static Etudiant GetEtudiantById(int id)
        {
            Etudiant etudiant = new Etudiant();
            string strSQL = @"select * from Etudiant where UserId=@Id";
            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                {
                    cnx.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cnx);
                    cmd.Parameters.AddWithValue("@Id", id);
                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        etudiant = ReaderToObject(dr);
                    }
                    dr.Close();
                    cnx.Close();
                }
            }
            catch (Exception) { }
            return etudiant;
        }

        public static int insertEtudiant(Etudiant e)
        {
            int n = 0;
            string strSQL = @"insert into Enseignant (Nom,Prenom,CIN,Num_tel,Email,Num_inscription, Id_niveau, Id_classe," +
            "Id_groupe, Id_specialite, @id_authentification values (@nom,@prenom,@cin,@num_tel,@email,@Num_inscription,@id_niveau," +
            "@id_classe,@id_groupe,@id_specialite,@ id_authentification)";
            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                {
                    cnx.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cnx);
                    cmd.Parameters.AddWithValue("@cin", e.Cin);
                    cmd.Parameters.AddWithValue("@nom", e.Nom);
                    cmd.Parameters.AddWithValue("@prenom", e.Prenom);
                    cmd.Parameters.AddWithValue("@num_tel", e.Num_tel);
                    cmd.Parameters.AddWithValue("@email", e.Email);
                    cmd.Parameters.AddWithValue("@num_inscription", e.Num_inscription);

                    cmd.Parameters.AddWithValue("@id_niveau", e.niveau.Id);
                    cmd.Parameters.AddWithValue("@id_classe", e.classe.Id);
                    cmd.Parameters.AddWithValue("@id_groupe", e.groupe.Id);
                    cmd.Parameters.AddWithValue("@id_authentification", e.authentification.Id);
                    cmd.Parameters.AddWithValue("@id_specialite", e.specialite.Id);
                    n = cmd.ExecuteNonQuery();
                    cnx.Close();
                }
            }
            catch (Exception) { }
            return n;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        public static int updateEtudiant(Etudiant e)
        {
            int n = 0;

            string strSQl = @"UPDATE Enseignant SET (@nom IS NULL OR Nom=@nom), (@prenom IS NULL OR Prenom=@prenom)," +
            "(@cin IS NULL OR Cin=@cin),(@num_tel IS NULL OR Num_tel=@num_tel),(@email IS NULL OR Email=@email), " +
            "(@num_inscription IS NULL OR Num_inscription=@num_inscription), Id_niveau=@id_niveau, Id_classe=@id_classe," +
            "Id_groupe=@id_groupe, Id_authentification=@id_authentification, Id_specialite=@id_specialite)";
            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                {
                    cnx.Open();

                    SqlCommand cmd = new SqlCommand(strSQl, cnx);
                    cmd.Parameters.AddWithValue("@cin", e.Cin);
                    cmd.Parameters.AddWithValue("@nom", e.Nom);
                    cmd.Parameters.AddWithValue("@prenom", e.Prenom);
                    cmd.Parameters.AddWithValue("@num_tel", e.Num_tel);
                    cmd.Parameters.AddWithValue("@email", e.Email);
                    cmd.Parameters.AddWithValue("@num_inscription", e.Num_inscription);

                    cmd.Parameters.AddWithValue("@id_niveau", e.niveau.Id);
                    cmd.Parameters.AddWithValue("@id_classe", e.classe.Id);
                    cmd.Parameters.AddWithValue("@id_groupe", e.groupe.Id);
                    cmd.Parameters.AddWithValue("@id_authentification", e.authentification.Id);
                    cmd.Parameters.AddWithValue("@id_specialite", e.specialite.Id);
                    n = cmd.ExecuteNonQuery();

                    cnx.Close();
                }
            }
            catch (Exception) { }
            return n;
        }



        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <param name="idAuthentif"></param>
        /// <returns></returns>
        public static int deletEtudiant(int id, int idAuthentif)
        {
            int n = 0;
            string strSQL = @"delete from Etudiant where id=@id)";
            string strSQl2 = @"UPDATE Authentification SET ACTIF='0' where id=@idAuth and ACTIF=1";
            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                {
                    cnx.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cnx);
                    SqlCommand cmd2 = new SqlCommand(strSQl2, cnx);
                    cmd.Parameters.AddWithValue("@id", id);
                    cmd2.Parameters.AddWithValue("@idAuth", idAuthentif);
                    if (cmd.ExecuteNonQuery() > 0)
                        n = cmd2.ExecuteNonQuery();
                    cnx.Close();
                }
            }
            catch (Exception) { }
            return n;
        }

    }
}
